package com.ws.shop.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.stereotype.Repository;

import com.ws.shop.dao.CommonDao;
import com.ws.shop.entity.Article;
import com.ws.shop.entity.Auction;
import com.ws.shop.entity.Paths;
import com.ws.shop.entity.Sunpic;
import com.ws.shop.util.JdbcUtil;

@Repository(value="commonDao")
public class CommonDaoImpl extends BaseDaoImpl implements CommonDao {
	
	private static Logger log = Logger.getLogger(CommonDaoImpl.class);
	
	private String auctionfiled = "a.id,a.sn,a.gid,a.goodsname,a.goodsprice,a.goods_pic,a.starttime,a.endtime,a.ordertime,a.startprice,a.addsecond,a.addprice,a.usebeat,a.pointc,a.costprice,a.recommend,a.auctioncount,a.realprice,a.auctionuname,a.uid,a.robots,a.robotspromote,a.min,a.max,a.target,a.agoid,a.agopic,a.delaytype,"
		+"a.type,a.rebate,a.boutlimit,a.iskeep,a.beforebout,a.discount,a.isdiscount,a.isfree,a.maxaddsecond,a.state";
	
	private String articlefiled = "a.id,a.cid,a.title,a.link,a.order,a.keywords,a.description,a.edittime,a.body,a.state";
	
	private String sunpicfiled = " s.id ,s.uid,s.oid ,s.name ,s.aid ,s.speech ,s.comments ,s.state ,s.addtime ,s.reward ,s.link";
	
	public Map<String,List> loadCmmonInfo(){
		
		final String auctions = " select " + auctionfiled + ",ac.thumbnail from jvf_auction as a  left join jvf_accessory as ac on a.goods_pic = ac.id where a.state = 1  order by a.endtime limit 20";
	    final String articles = " select " + articlefiled + " from jvf_article as a where cid = 100001 and state = 1  order by a.order desc limit 6";
	    final String auctioneds = " select " + auctionfiled + ",ac.thumbnail from jvf_auction as a  left join jvf_accessory as ac on a.goods_pic = ac.id where a.state >= 2  order by a.endtime limit 5";
	    final String sunpics = " select " + sunpicfiled + ",a.sn,a.realprice,a.goodsname,u.head,u.addtime as regtime from jvf_sunpic as s "+
	    "join jvf_auction as a on s.aid = a.id join jvf_user as u on s.uid = u.id where s.state = 1 order by s.id desc limit 4 ";
	    
		return (Map<String,List>)this.execute(
				new ConnectionCallback() {
					public Object doInConnection(Connection connect) {
						Statement smt = null;
						ResultSet rs = null;
						try {
							smt = connect.createStatement();
							rs = smt.executeQuery(auctions);
							Map<String,List> maplist = new HashMap<String,List>();
							List<Auction> auctionlist = new LinkedList<Auction>();
							while(rs.next()){
								Auction a = getAuction(rs);
								auctionlist.add(a);
							}
							maplist.put("auctioning", auctionlist);
							log.info("++++++++++++++++++auctioning++++++++++++++++++++++++");
							JdbcUtil.close(rs, smt, null);
							List<Auction> aucedlist = new LinkedList<Auction>();
							smt = connect.createStatement();
							rs = smt.executeQuery(auctioneds);
							while(rs.next()){
								Auction a = getAuction(rs);
								aucedlist.add(a);
							}
							maplist.put("auctioned", aucedlist);
							log.info("++++++++++++++++++auctioned++++++++++++++++++++++++");
							JdbcUtil.close(rs, smt, null);
							List<Article> artilist = new LinkedList<Article>();
							smt = connect.createStatement();
							rs = smt.executeQuery(articles);
							while(rs.next()){
								Article article = getArticle(rs);
								artilist.add(article);
							}
							maplist.put("article", artilist);
							log.info("++++++++++++++++++article++++++++++++++++++++++++");
							JdbcUtil.close(rs, smt, null);
							List<Sunpic> sunpiclist = new LinkedList<Sunpic>();
							smt =connect.createStatement();
							rs = smt.executeQuery(sunpics);
							while(rs.next()){
								Sunpic sunpic = getSunpic(connect,rs);
								sunpiclist.add(sunpic);
							}
							maplist.put("sunpic", sunpiclist);
							log.info("++++++++++++++++++sunpic++++++++++++++++++++++++");
							return maplist;
						} catch (Exception e) {
							e.printStackTrace();
							log.error("loadCmmonInfo!",e);
							return null;
						} finally {
							JdbcUtil.close(rs, smt, null);
			      		}
			}
		});
    }

    private Auction getAuction(ResultSet rs) throws SQLException{
    	Auction a = new Auction();
		a.setId(rs.getLong(1));
		a.setSn(rs.getString(2));
		a.setGid(rs.getInt(3));
		a.setGoodsname(rs.getString(4));
		a.setGoodsprice(rs.getFloat(5));
		a.setGoods_pic(rs.getInt(6));
		a.setStarttime(rs.getTimestamp(7));
		a.setEndtime(rs.getTimestamp(8));
		a.setOrdertime(rs.getTimestamp(9));
		a.setStartprice(rs.getFloat(10));
		a.setAddsecond(rs.getInt(11));
		a.setAddprice(rs.getFloat(12));
		a.setUsebeat(rs.getInt(13));
		a.setPointc(rs.getInt(14));
		a.setCostprice(rs.getFloat(15));
		a.setRecommend(rs.getInt(16));
		a.setAuctioncount(rs.getInt(17));
		a.setRealprice(rs.getFloat(18));
		a.setAuctionuname(rs.getString(19));
		a.setUid(rs.getInt(20));
		a.setRobots(rs.getInt(21));
		a.setRobotspromote(rs.getFloat(22));
		a.setMin(rs.getInt(23));
		a.setMax(rs.getInt(24));
		a.setTarget(rs.getFloat(25));
		a.setAgoid(rs.getInt(26));
		a.setAgopic(rs.getInt(27));
		a.setDelaytype(rs.getInt(28));
		a.setType(rs.getInt(29));
		a.setRebate(rs.getInt(30));
		a.setBoutlimit(rs.getInt(31));
		a.setIskeep(rs.getInt(32));
		a.setBeforebout(rs.getInt(33));
		a.setDiscount(rs.getInt(34));
		a.setIsdiscount(rs.getInt(35));
		a.setIsfree(rs.getInt(36));
		a.setMaxaddsecond(rs.getInt(37));
		a.setState(rs.getInt(38));
		a.setThumbnail(rs.getString(39));
		return a;
    }
    

    private Article getArticle(ResultSet rs) throws SQLException{
    	Article a = new Article();
		a.setId(rs.getInt(1));
		a.setCid(rs.getInt(2));
		a.setTitle(rs.getString(3));
		a.setLink(rs.getString(4));
		a.setOrder(rs.getInt(5));
		a.setKeywords(rs.getString(6));
		a.setDescription(rs.getString(7));
		a.setEdittime(rs.getTimestamp(8));
		a.setBody(rs.getString(9));
		a.setState(rs.getInt(10));
		return a;
    }
    
    
    private Sunpic getSunpic(Connection connect,ResultSet rs) throws SQLException{
    	   Sunpic sunpic = new Sunpic();
		   long sid = rs.getInt(1);
		   int uid = rs.getInt(2);
		   sunpic.setId(sid);
		   sunpic.setUid(uid);
		   sunpic.setOid(rs.getLong(3));
		   sunpic.setName(rs.getString(4));
		   sunpic.setAid(rs.getLong(5));
		   sunpic.setSpeech(rs.getString(6));
		   sunpic.setComments(rs.getString(7));
		   sunpic.setState(rs.getInt(8));
		   sunpic.setAddtime(rs.getTimestamp(9));
		   sunpic.setReward(rs.getInt(10));
		   sunpic.setLink(rs.getInt(11));
		   sunpic.setSn(rs.getString(12));
		   sunpic.setRealprice(rs.getDouble(13));
		   sunpic.setGoodsname(rs.getString(14));
		   sunpic.setHead(rs.getString(15));
		   sunpic.setRegtime(rs.getTimestamp(16));
		   String s = "select ac.path,ac.thumbnail from  jvf_accessory_relation as ar join jvf_accessory as ac on ar.accessoryid = ac.id where ar.tables = 'Sunpic' and ar.relationid = " + sid;
		   log.info("accessory relation sql : " + s);
		   Statement stmt = connect.createStatement();
		   ResultSet r = stmt.executeQuery(s);
		   List<Paths> pathslist = new ArrayList<Paths>();
		   while(r.next()){
			   Paths paths = new Paths();
			   paths.path = r.getString(1);
			   paths.thumbnail = r.getString(2);
			   pathslist.add(paths);
		   }
		   JdbcUtil.close(r, stmt, null);
		   sunpic.setPaths(pathslist);
		   return sunpic;
    }
    
}
